package com.mengye.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mengye.bean.CourseInfoBean;
import com.mengye.bean.TeacherInfoBean;
import com.mengye.model.UserInfoModel;
import com.mengye.utils.MyUtils;

public class SendThread extends Thread {// 连接驱动
	final static String DRIVER = "com.mysql.jdbc.Driver";
	// 链接地址
	final static String URL = "jdbc:mysql://localhost:3306/test";
	// 数据库用户名
	final static String USERNAME = "root";
	// 数据库用户密码
	final static String PSW = "111111";
	// 声明连接
	static Connection connection = null;
	// 定义statement对象
	static Statement stmt = null;
	// 声明结果集，默认为null
	static ResultSet rs = null;

	public static List<TeacherInfoBean> QueryAllTeaInfo(String sql) {
		List<TeacherInfoBean> teaLst = new ArrayList<TeacherInfoBean>();
		try {
			// 加载驱动
			Class.forName(DRIVER);
			// 进行数据库连接
			connection = DriverManager.getConnection(URL, USERNAME, PSW);
			// 获取statement对象
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				TeacherInfoBean tIB = new TeacherInfoBean();
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(1));
				String id = rs.getString(1);
				System.out.println(id);
				tIB.setT_id(id);
				System.out.println(tIB.getT_id());
				tIB.setT_Name(rs.getString(2));
				tIB.setT_Email(rs.getString(4));
				teaLst.add(tIB);
			}

		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动异常");
		} catch (SQLException e) {
			System.out.println("数据库连接异常");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("其他异常");
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return teaLst;

	}

	public static List<CourseInfoBean> QueryAllCourseInfo(String id) {
		List<CourseInfoBean> courseLst = new ArrayList<CourseInfoBean>();
		try {
			// 加载驱动
			Class.forName(DRIVER);
			// 进行数据库连接
			connection = DriverManager.getConnection(URL, USERNAME, PSW);
			// 获取statement对象
			stmt = connection.createStatement();
			String sql = "SELECT * FROM course WHERE TO_DAYS(c_date)-TO_DAYS(NOW()) = 1 and t_id = " + id;
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				CourseInfoBean cIB = new CourseInfoBean();
				cIB.setC_Con(rs.getString(5));
				cIB.setC_Date(rs.getDate(2));
				cIB.setC_Name(rs.getString(1));
				cIB.setC_Place(rs.getString(6));
				cIB.setC_Time(rs.getString(3));
				cIB.setT_id(rs.getString(4));
				courseLst.add(cIB);
			}
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动异常");
		} catch (SQLException e) {
			System.out.println("数据库连接异常");
		} catch (Exception e) {
			System.out.println("其他异常");
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return courseLst;

	}

	@Override
	public void run() {
		while (true) {
			MailUtils mail = new MailUtils();
			String sql = "select * from teacher";
			List<TeacherInfoBean> teaInfoLst = QueryAllTeaInfo(sql);

			List<CourseInfoBean> courseInfoLst = new ArrayList<CourseInfoBean>();
			// 添加课程
			for (TeacherInfoBean teacherInfoBean : teaInfoLst) {
				System.out.println(teacherInfoBean.toString());
				for (CourseInfoBean c : QueryAllCourseInfo(teacherInfoBean.getT_id())) {
					courseInfoLst.add(c);
				}
			}

			// 查询并发送
			for (TeacherInfoBean teacherInfoBean : teaInfoLst) {

				String text=" 尊敬的" + teacherInfoBean.getT_Name() + "老师，您明天的课程信息如下\n    ";
				String text1="";
				for (CourseInfoBean courseInfoBean : courseInfoLst) {
					if (courseInfoBean.getT_id().equals(teacherInfoBean.getT_id())) {
						text1 =  "————————"+"课程名称：" + courseInfoBean.getC_Name() + ",\n课程地点：" + courseInfoBean.getC_Place() + ",\n课程时间："
								+ courseInfoBean.getC_Date().toString() + "  " + courseInfoBean.getC_Time() + ",\n课程内容："
								+ courseInfoBean.getC_Con();
					}
					text += text1;
				}
				if(!text1.equals("")){
					mail.sendMail(teacherInfoBean.getT_Email(), text, "课程提醒");
					System.out.println("已发送");
				}
			}
			try {
				Thread.sleep(86400000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
